#include "mainwindow.h"
#include "ui_mainwindow.h"
#include "testsqlmodel.h"
#include "loginview.h"
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QDebug>
#include <QSqlError>

MainWindow::MainWindow(QWidget *parent)
    : QMainWindow(parent)
    , ui(new Ui::MainWindow)
{
    ui->setupUi(this);
    //当前qt支持的数据库
    qDebug() << "当前qt支持的数据库:";
    qDebug() << QSqlDatabase::drivers();

    //sqlite数据库
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("testsql.db");
    if (db.open())
    {
        qDebug() << "open database";
    }
    else
    {
        return;
    }
}

MainWindow::~MainWindow()
{
    delete ui;
}


void MainWindow::on_btn_create_student_clicked()
{
#if 0
    QString sqlStr = "CREATE TABLE user ( id integer PRIMARY KEY AUTOINCREMENT,name TEXT,password TEXT);";
    QSqlQuery myQuery;
    if (myQuery.exec(sqlStr))
    {
        qDebug() << "create table student user";
    }
    else
    {
        qDebug() << "faild" << myQuery.lastError().text();
    }
#endif
#if 0
    QSqlQuery query;
    // 创建student2表
    query.exec("create table student2 (id int primary key, name varchar, course int)");
    query.exec("insert into student2 values(1, '李', 10)");
    query.exec("insert into student2 values(2, '马', 11)");
    query.exec("insert into student2 values(3, '孙', 12)");
    // 创建course表
    query.exec("create table course (id int primary key, name varchar, teacher varchar)");
    query.exec("insert into course values(10, '数学', '王老师')");
    query.exec("insert into course values(11, '英语', '张老师')");
    query.exec("insert into course values(12, '计算机', '李老师')");
#endif

    //LOGIN
    LoginView *pView = new LoginView(this);
    this->hide();
    pView->show();
}

void MainWindow::on_btn_query_clicked()
{
    QString strQuery = "SELECT * FROM student";
    QSqlQuery query;
    if (query.exec(strQuery))
    {
        while (query.next()) {
            QString strInfo0 = query.value(0).toString();
            QString strInfo1 = query.value(1).toString();
            QString strInfo2 = query.value(2).toString();
            qDebug() << "strInfo0" <<strInfo0 << " " << "strInfo1" <<strInfo1 << " " << "strInfo2" <<strInfo2;
        }
    }
}

void MainWindow::on_btn_modify_clicked()
{
    QString strModify = "UPDATE student SET name='LISI' where id=2";
    QSqlQuery query;
    if (query.exec(strModify))
    {
        qDebug() << "modify success";
    }
    else
    {
        qDebug() << "modify failed";
    }
}

void MainWindow::on_btn_insert_clicked()
{
#if 0
    //第一种
    QString strInsert = "INSERT INTO student(name, age) VALUES('李四', 12)";
    QSqlQuery query;
    if (query.exec(strInsert))
    {
        qDebug() << "insert success";
    }
    else
    {
        qDebug() << "insert failed";
    }
#endif
#if 0
    //第二种 名称绑定
    QString strInsert = "INSERT INTO student(name, age) VALUES(:name, :age)";
    QSqlQuery insertQuery;
    insertQuery.prepare(strInsert);
    insertQuery.bindValue(":name", "王五");
    insertQuery.bindValue(":age", 23);
    if (insertQuery.exec())
    {
        qDebug() << "second insert success";
    }
    else
    {
        qDebug() << "second insert failed";
    }
#endif
#if 0
    //第三种 位置绑定
    QString strInsert = "INSERT INTO student(name, age) VALUES(?, ?)";
    QSqlQuery insertQuery;
    insertQuery.prepare(strInsert);
    insertQuery.addBindValue("赵六");
    insertQuery.addBindValue(32);
    if (insertQuery.exec())
    {
        qDebug() << "third insert success";
    }
    else
    {
        qDebug() << "third insert failed";
    }
#endif

    //插入多条数据
    QString strInsert = "INSERT INTO student(name, age) VALUES(?, ?)";
    QVariantList vNames;
    vNames << "zhao" << "qian" << "sun" << "li";
    QVariantList vAges;
    vAges << 23 << 33 << 43 << 21;
    QSqlQuery insertQuery;
    insertQuery.prepare(strInsert);
    insertQuery.addBindValue(vNames);
    insertQuery.addBindValue(vAges);
    if (insertQuery.execBatch())
    {
        qDebug() << "insert 4 success";
    }
    else
    {
        qDebug() << "insert 4 failed";
    }
}

void MainWindow::on_btn_delete_clicked()
{
    QString strDelete = "DELETE FROM student WHERE id=4";
    QSqlQuery query;
    if (query.exec(strDelete))
    {
        qDebug() << "delete success";
    }
    else
    {
        qDebug() << "delete failed";
    }
}


void MainWindow::on_btn_jump_clicked()
{
    TestSqlModel *pView = new TestSqlModel();
    this->hide();
    pView->show();
}

